package com.example.springboottools.utils;

import cn.hutool.core.map.MapUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class PoiExcelUtils {
    private final static String XLS = "xls";
    /**
     * Excel 2007
     */
    private final static String XLSX = "xlsx";
    public static void main(String[] args) {
        try {
            // excel添加列
            List<String> rowList1 = new ArrayList<>();
            rowList1.add("固定列");
            List<String> rowList2 = new ArrayList<>();
            rowList2.add("自定义");
            List<String> rowList3 = new ArrayList<>();
            rowList3.add("京东店铺");
            rowList3.add("京东链接");
            String s = PoiExcelUtils.addCell("D:\\staples\\日常记录\\工作记录\\2024\\03\\商品新增上架\\接口开发\\模板\\测试数据\\vendor_create_product-spicel.xls",
                    "D:\\staples\\日常记录\\工作记录\\2024\\03\\商品新增上架\\接口开发\\模板\\测试数据\\vendor_create_product-spicel0612.xls"
                    , rowList1, rowList2, rowList3, 0);
            System.out.printf(s);
            //刷新指定Excel的sheet数据
            /*String filePath = "D:\\staples\\日常记录\\工作记录\\2024\\03\\商品新增上架\\接口开发\\测试数据\\vendor_create_product-nomal-new.xls";
            String sheetName = "常用税收编码";
            List<Map<String, Object>> dataList = new ArrayList<>();
            for (int i = 0; i < 20000; i++) {
                Map<String, Object> map = MapUtil.newHashMap();
                map.put("999","999");
                map.put("666","7777");
                map.put("6667","7777");
                map.put("6665","7777");
                map.put("6664","7777");
                map.put("6663","7777");
                map.put("6662","7777");
                map.put("6661","7777");
                map.put("6669","7777");
                dataList.add(map);
            }
            String [] columnName = new String[]{"999","666","6667","6665","6664","6663","6662","6661","6669"};

            ByteArrayOutputStream byteArrayOutputStream = refreshNormalExcel(filePath, sheetName,columnName, dataList);
           String outPath = "D:\\staples\\日常记录\\工作记录\\2024\\03\\商品新增上架\\接口开发\\测试数据\\vendor_create_product-nomal-new1.xls";
            byteArrayOutputStream.writeTo(new FileOutputStream(outPath));*/
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }



    public static ByteArrayOutputStream refreshNormalExcel(String filePath,String sheetName,
                                                           String[] columnName,List<Map<String, Object>> dataList) throws IOException {
        FileInputStream templateFileStream = new FileInputStream(filePath);
        Workbook workbook = new HSSFWorkbook(templateFileStream);

        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            sheet = workbook.createSheet(sheetName);
        }
        if (!dataList.isEmpty()) {
           // sheet.removeRow(sheet.getRow(0));
            for (int i = sheet.getLastRowNum(); i >= 1; i--) {
                sheet.removeRow(sheet.getRow(i));
            }
        }

        // 4. 将数据写入 sheet
        int rowNum = 1;
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowNum++);
            //int cellNum = 0;
            for (int i = 0; i < columnName.length; i++) {
                Cell cell = row.createCell(i);
                Object value = data.get(columnName[i]);
                // 根据数据类型设置单元格值
                if (value instanceof String) {
                    cell.setCellValue((String) value);
                } else if (value instanceof Number) {
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Boolean) {
                    cell.setCellValue((Boolean) value);
                } else {
                    cell.setCellValue(value.toString()); // 或者选择其他合适的默认处理方式
                }
            }
        }

        // 5. 关闭输入流并写出到 ByteArrayOutputStream
        templateFileStream.close();
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream;
    }



    public static String addCell(String filePath,String outPath,List<String> rowList1
            ,List<String> rowList2,List<String> rowList3,int sheetNum)
            throws IOException {

        Workbook workbook = null;

        String extensionName = getFileSuffix(filePath);
        FileInputStream fs = new FileInputStream(filePath);
        if (extensionName.toLowerCase().equals(XLS)) {
            workbook = new HSSFWorkbook(fs);
        } else if (extensionName.toLowerCase().equals(XLSX)) {
            workbook = new XSSFWorkbook(fs);
        }
        Sheet sheetAt = workbook.getSheetAt(sheetNum);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        Row row = sheetAt.getRow(0);
        Row row1 = sheetAt.getRow(1);
        int lastCellNum = (int)row.getLastCellNum();
        lastCellNum = getLastCellNum(workbook,sheetAt,rowList1, lastCellNum, row, cellStyle, row1);
        lastCellNum = getLastCellNum(workbook,sheetAt,rowList2, lastCellNum, row, cellStyle, row1);
        lastCellNum = getLastCellNum(workbook,sheetAt,rowList3, lastCellNum, row, cellStyle, row1);
        //ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        FileOutputStream outputStream=null;
        try {
            outputStream=new FileOutputStream(outPath);
            workbook.write(outputStream);
        } catch (Exception ignored){

        }finally {
            if (outputStream != null) {
                outputStream.flush();
                outputStream.close();
            }
            workbook.close();
            fs.close();
        }
        return outPath;
    }
    public static  ByteArrayOutputStream getByte(String filePath)
            throws IOException {

        Workbook workbook = null;

        String extensionName = getFileSuffix(filePath);
        FileInputStream fs = new FileInputStream(filePath);
        if (extensionName.toLowerCase().equals(XLS)) {
            workbook = new HSSFWorkbook(fs);
        } else if (extensionName.toLowerCase().equals(XLSX)) {
            workbook = new XSSFWorkbook(fs);
        }
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            workbook.write(outputStream);
        } catch (Exception ignored){

        }finally {
            if (outputStream != null) {
                outputStream.flush();
                outputStream.close();
            }
            workbook.close();
            fs.close();
        }
        return outputStream;
    }

    public static String getFileSuffix(String fileName) {
        return fileName.substring(fileName.lastIndexOf(".") + 1,
                fileName.length());
    }
    private static int getLastCellNum(Workbook workbook,Sheet sheet,List<String> rowList1, int lastCellNum, Row row, CellStyle cellStyle, Row row1) {
        if (CollectionUtils.isNotEmpty(rowList1)){
            for (int i = 0; i < rowList1.size(); i++) {
                int i1 = lastCellNum + i;
                Cell cell = row.createCell(i1);
                cell.setCellValue("MN" + i1);
                cell.setCellStyle(cellStyle);
                Cell cell1 = row1.createCell(i1);
                cell1.setCellValue(rowList1.get(i));
                cell1.setCellStyle(cellStyle);
                // 创建DataValidationHelper对象
                DataValidationHelper validationHelper = sheet.getDataValidationHelper();
                // 创建DataValidationConstraint对象，设置下拉列表选项
                String[] options = {"自营", "旗舰", "测试"};
                DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(options);
                // 定义数据验证区域，覆盖当前列的所有行
                CellRangeAddressList addressList = new CellRangeAddressList(
                        2,
                        65535,
                        i1,
                        i1
                );
                // 创建DataValidation对象
                DataValidation validation = validationHelper.createValidation(constraint, addressList);

// 设置数据验证属性
                validation.setSuppressDropDownArrow(false);
                validation.setShowErrorBox(true);
               // validation.createPromptBox("可选值", "10-测试1\n 20-测试2 \n 30-测试3 \n 40-测试4 \n 50-测试5 \n              ");
                //validation.setShowPromptBox(true);

// 将数据验证规则添加到工作表
                sheet.addValidationData(validation);


            }
            lastCellNum = lastCellNum + rowList1.size();
        }
        return lastCellNum;
    }
    /**
     * 向已知表中插入数据，累计追加
     * 写入前先判断表是否存在，表中是否有数据
     *
     * @param filePath excel的路径 D:\\123.xls
     * @throws Exception
     */
    public static void inserSheetData(String filePath,String outPath) throws Exception {
        // String filePath = "D:\\123.xls";
        FileInputStream fs = new FileInputStream(filePath);

        //使用POI提供的方法得到excel的信息
        //POIFSFileSystem fileSystem = new POIFSFileSystem(fs);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fs);
        //获取到工作表，因为一个excel可能有多个工作表
        HSSFSheet InsertSheet = hssfWorkbook.getSheetAt(0);
        HSSFRow row1 = InsertSheet.getRow(0);
        HSSFRow row2 = InsertSheet.getRow(1);
        short lastCellNum = row1.getLastCellNum();
        short lastCellNum1 = row2.getLastCellNum();
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        HSSFCell cell = row1.createCell(lastCellNum);
        cell.setCellValue("MN"+lastCellNum);
        cell.setCellStyle(cellStyle);
        HSSFCell cell1 = row2.createCell(lastCellNum1);
        cell1.setCellValue("年龄");
        cell1.setCellStyle(cellStyle);
        FileOutputStream out = new FileOutputStream(outPath);
        out.flush();
        hssfWorkbook.write(out);
        out.close();
    }

}
